﻿using System;
using System.Linq;
using System.IO;
using OpenExcel.OfficeOpenXml;

namespace OpenExcel_Examples
{
    public static class Example2
    {
        public static void Run()
        {
            Console.WriteLine("=== Running Example2 ===");
            string previousFile = Path.Combine(Program.OutputDirectory, "example1.xlsx");
            string outputFile = Path.Combine(Program.OutputDirectory, "example2.xlsx");
            File.Copy(previousFile, outputFile, true);
            using (ExcelDocument doc = ExcelDocument.Open(outputFile, true))
            {
                ExcelWorksheet wsheet = doc.Workbook.Worksheets["My Order"];

                // Insert new items
                wsheet.InsertRows(5, 3);

                wsheet.Cells[5, 1].Value = "Coffee";
                wsheet.Cells[5, 2].Value = 10.00;
                wsheet.Cells[5, 3].Value = 1;

                wsheet.Cells[6, 1].Value = "Donut";
                wsheet.Cells[6, 2].Value = 15.00;
                wsheet.Cells[6, 3].Value = 2;

                wsheet.Cells[7, 1].Value = "Mints";
                wsheet.Cells[7, 2].Value = 1.00;
                wsheet.Cells[7, 3].Value = 5;

                // Copy Amount formula to new items
                // Formula will automatically translate when copied.
                ExcelCellFormula fAmt = wsheet.Cells[4, 4].Formula;
                fAmt.CopyTo(5, 4);
                fAmt.CopyTo(6, 4);
                fAmt.CopyTo(7, 4);

                // Clear style from "TOTAL" label
                wsheet.Cells["A9"].Style = null;

                // At this point, total formulas in both sheets are already updated

                // Formulas also update when inserting/pushing columns
                // Insert differs from Push; the latter does not copy
                // previous row/column style
                wsheet.PushColumn("C");
            }
        }
    }
}
